using System;
using System.Data;
using System.Data.SqlClient;
using SalesAndStoreApp.DAL.DAO;

namespace SalesAndStoreApp.DAL.Gateway
{
    public class SalesGateway:DbGateway
    {
        private InventoryGateway inventoryGateway = new InventoryGateway();

        public bool Save(ProductCarriage productCarriage)
        {
            Connection.Open();  
            using (var transaction = Connection.BeginTransaction())
            {
                Command.Transaction = transaction;
                try
                {
                    InsertInSales(productCarriage);
                    productCarriage.Id = GetLastInsertedId();
                    InsertProductsInSalesDetails(productCarriage);
                    inventoryGateway.InsertOrDeduct(productCarriage);
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    transaction.Rollback();
                    throw new Exception("Save couldn't performed due to system error!", exception);
                }
            }
        }

        private void InsertProductsInSalesDetails(ProductCarriage productCarriage)
        {
            try
            {
                string query = "INSERT INTO SaleDetails VALUES(@saleId,@productId,@qty)";
                Command.CommandText = query;
                foreach (var product in productCarriage.Products)
                {
                    Command.Parameters.Clear();
                    Command.Parameters.Add("@saleId", SqlDbType.Int);
                    Command.Parameters["@saleId"].Value = productCarriage.Id;
                    Command.Parameters.Add("@productId", SqlDbType.Int);
                    Command.Parameters["@productId"].Value = product.Id;
                    Command.Parameters.Add("@qty", SqlDbType.Int);
                    Command.Parameters["@qty"].Value = product.Inventory.Quantity;
                    Command.ExecuteNonQuery();
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred while trying to insert sales information", exception);
            }
        }

        private int GetLastInsertedId()
        {
            try
            {
                const string query = "SELECT * FROM Sale ORDER BY Id DESC";
                Command.CommandText = query;
                int lastId = 0;
                using (SqlDataReader dataReader = Command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        lastId = int.Parse(dataReader["Id"].ToString());
                        break;
                    }
                }
                if (lastId == 0)
                {
                    throw new Exception("There is no Order Inserted while trying to retrieve a Order");
                }
                return lastId;
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred in internal database operation at sales module please contact with your vendor",exception);
            }
        }

        private void InsertInSales(ProductCarriage productCarriage)
        {
            try
            {
                const string query = "INSERT INTO Sale VALUES(@saleDate,@totalPrice)";
                Command.CommandText = query;
                Command.Parameters.Add("@saleDate", SqlDbType.Date);
                Command.Parameters["@saleDate"].Value = productCarriage.Date;
                Command.Parameters.Add("@totalPrice", SqlDbType.Real);
                Command.Parameters["@totalPrice"].Value = productCarriage.TotalPrice;
                Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception("Exception Occurred while trying to insert sales information",exception);
            }
        }
    }
}